------------------------------------------------------------------------------------
--    TABLE NAME : dbo.ServerSQL
--   DESCRIPTION : The Server table contains information about the MS SQL instance
--                 running on a host.  Data in this table is collected via PowerShell
--                 by executing Upsert-ServerSQL.ps1
------------------------------------------------------------------------------------
--  POPULATED BY : Upsert-ServerSQL.ps1
--  DAYS TO KEEP : Infinite
------------------------------------------------------------------------------------
-- CHANGE HISTORY:
-- DATE        MODIFIED   DESCRIPTION   
------------------------------------------------------------------------------------
-- 08.04.2010  SYoung     Initial creation
------------------------------------------------------------------------------------
CREATE TABLE [dbo].[ServerSQL](
       [ServerSQLID] [int] IDENTITY(200000,1) NOT NULL,
       [InstanceName] [varchar](128) NOT NULL,
       [ServerOSID] [int] NULL,
       [Environment] [char](1) NOT NULL,
       [TcpPort] [varchar](10) NULL,
       [ServerNetworkProtocols] [varchar](128) NULL,
       [Type] [varchar](10) NULL,
       [Edition] [varchar](128) NULL,
       [Version] [varchar](20) NULL,
       [ServicePack] [varchar](10) NULL,
       [StartupParameters] [varchar](512) NULL,
       [SystemDbDevice] [varchar](512) NULL,
       [ErrorLogLocation] [varchar](512) NULL,
       [Collation] [varchar](128) NULL,
       [MinMemory] [bigint] NULL,
       [MaxMemory] [bigint] NULL,
       [AWEEnabled] [bit] NULL,
       [MaxUserConnections] [int] NULL,
       [CreateDate] [smalldatetime] NULL,
       [UpdateDate] [smalldatetime] NULL,
       [Active] [tinyint] NULL,
CONSTRAINT [PK_ServerSQL] PRIMARY KEY CLUSTERED (
       [ServerSQLID] ASC)
)
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID of the SQL Server.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'ServerSQLID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'MSSQLSRVER for the default instance. For a named instance, instanceName = serverName.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'InstanceName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Contains the ID of the standalone host of the SQL Server.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'ServerOSID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Status of this server. Possible values include D (Development), Q (Quality Assurance), P (Production), U (User Acceptance Testing), I (Integration), R (Disaster Recovery).' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'Environment'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'TCP port on which the SQL Server is listening.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'TcpPort'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Network protocols used by the SQL server.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'ServerNetworkProtocols'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'2008, 2005, or 2000.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Enterprise, Standard, or Developer.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'Edition'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'10.0.1300' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'Version'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Service pack installed on the SQL Server.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'ServicePack'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Parameters used at SQL Server startup.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'StartupParameters'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Path to the directory that contains system databases.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'SystemDbDevice'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Path to the SQL Server Errorlog.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'ErrorLogLocation'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Collation of the SQL Server.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'Collation'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Minimum memory configurated for the SQL Server.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'MinMemory'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Maximum memory confugured for the SQL Server.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'MaxMemory'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates whether Address Windowing Extensions (AWE) is enabled.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'AWEEnabled'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Maximum number of user connections that are allowed to connect with the SQL Server at the same time.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'MaxUserConnections'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the server record was created.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'CreateDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the server record was last updated.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'UpdateDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'On/Off switch to know if the row should be included in queries.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL', @level2type=N'COLUMN',@level2name=N'Active'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The Server table contains information about the MS SQL instance running on a host.  Data in this table is collected via PowerShell by executing Upsert-ServerSQL.ps1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ServerSQL'
GO
ALTER TABLE [dbo].[ServerSQL] ADD  CONSTRAINT [DF_ServerSQL_Active]  DEFAULT ((1)) FOR [Active]
GO
